在SQL中,Group By没有Count(*),或者Sum(),Max(),avg(),...是什么意思,有什么用处?

2023-03-22 03:06| 来源: 网络整理| 查看: 265

回答问题

在 SQL 中,如果我们使用 Group By 而不使用 Count(*) 或 Sum() 等,那么结果如下:

mysql> select * from sentGifts; +--------+------------+--------+------+---------------------+--------+ | sentID | whenSent | fromID | toID | trytryWhen | giftID | +--------+------------+--------+------+---------------------+--------+ | 1 | 2010-04-24 | 123 | 456 | 2010-04-24 01:52:20 | 100 | | 2 | 2010-04-24 | 123 | 4568 | 2010-04-24 01:56:04 | 100 | | 3 | 2010-04-24 | 123 | NULL | NULL | 1 | | 4 | 2010-04-24 | NULL | 111 | 2010-04-24 03:10:42 | 2 | | 5 | 2010-03-03 | 11 | 22 | 2010-03-03 00:00:00 | 6 | | 6 | 2010-04-24 | 11 | 222 | 2010-04-24 03:54:49 | 6 | | 7 | 2010-04-24 | 1 | 2 | 2010-04-24 03:58:45 | 6 | +--------+------------+--------+------+---------------------+--------+ 7 rows in set (0.00 sec) mysql> select *, count(*) from sentGifts group by whenSent; +--------+------------+--------+------+---------------------+--------+----------+ | sentID | whenSent | fromID | toID | trytryWhen | giftID | count(*) | +--------+------------+--------+------+---------------------+--------+----------+ | 5 | 2010-03-03 | 11 | 22 | 2010-03-03 00:00:00 | 6 | 1 | | 1 | 2010-04-24 | 123 | 456 | 2010-04-24 01:52:20 | 100 | 6 | +--------+------------+--------+------+---------------------+--------+----------+ 2 rows in set (0.00 sec) mysql> select * from sentGifts group by whenSent; +--------+------------+--------+------+---------------------+--------+ | sentID | whenSent | fromID | toID | trytryWhen | giftID | +--------+------------+--------+------+---------------------+--------+ | 5 | 2010-03-03 | 11 | 22 | 2010-03-03 00:00:00 | 6 | | 1 | 2010-04-24 | 123 | 456 | 2010-04-24 01:52:20 | 100 | +--------+------------+--------+------+---------------------+--------+ 2 rows in set (0.00 sec)

每个“组”仅返回 1 行。使用“Group By”时没有“Count(*)”等是什么意思,它的用途是什么?谢谢。

Answers

默认情况下,MySQL 将返回执行查询时遇到的第一行的值。就像它使用arbitrary的默认聚合一样。

如果您有一个很长的列列表,并且您知道其中大多数都在重复,这将很有用,例如:

Login LongName City PhoneNr Time Dude Mr. Dude the 2nd Jerk Town 12345678 13:01 Dude Mr. Dude the 2nd Jerk Town 12345678 13:05 Dude Mr. Dude the 2nd Jerk Town 12345678 13:12

在这里你可以group by login:

select LongName, City, PhoneNr, max(Time) from Logins group by login

因为您知道Long Name依赖于Login,所以这将按预期工作。我知道的所有其他 DBMS 系统都要求您明确指定group by login, LongName, City, PhoneNr。即使在 MySQL 中,这也被认为是好的做法。

【本文地址】

公司简介

联系我们